/**
 * 
 */
package org.mhz.filetodb.main;

import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 * @author Mahan
 *
 * 
 */
public class FolderStructureToDB extends FolderTraversar
{
    private String mIndent = "";
    private boolean mIsRootFolder = false;
    private static Connection mConnection = null;
    private static Statement mStatement = null;
    
    private String mNodeId = null;
    private String mNodeName = null;
    private String mNodeType = null;
    private String mNodeParent = null;
    private String mNodeContentLocation = null;
    
    private int mRowId = 0;
        
	/**
     * @param aFileObject
     */
    public FolderStructureToDB(File aFileObject)
    {
        super(aFileObject);
        // TODO Auto-generated constructor stub
    }

    public void recursiveTraversal(final File fileObject) 
    {
        String insertSQL = null;
        String modifiedName = null;
        String fileName = null;
        
        FileInputStream fstream = null;
        DataInputStream in = null;
        BufferedReader br = null;
        
        try
        {                                    
            if (fileObject.isDirectory()) 
            {            
                mIndent = getIndent(fileObject);
                //System.out.println(mIndent + fileObject.getName());
                
                if (!mIsRootFolder)
                {
                    mNodeParent = "0";
                    mIsRootFolder = true;
                }
                else
                {
                    mNodeParent = getParentFolder();
                }
                
                modifiedName = getModifiedName(fileObject.getName());
                mNodeId = modifiedName;
                fileName = fileObject.getName().replaceAll(",", "");
                fileName = fileObject.getName().replaceAll("\'", "\\\\\'");
                mNodeName = fileName;
                mNodeType = null;                
                mNodeContentLocation = null;                                
                                
                insertSQL = createInsertIntoMenuHierarchySQL(mNodeId, mNodeName, mNodeType, mNodeParent, mNodeContentLocation);
                System.out.println(mIndent + " - Folder >" + insertSQL + "<");  
                
                // insert into the menu hierarchy table
                mStatement.executeUpdate(insertSQL);
     
                File allFiles[] = fileObject.listFiles();
                
                for (File aFile : allFiles) 
                {
                    recursiveTraversal(aFile);
                }
            } 
            else if (fileObject.isFile()) 
            {
                if (fileObject.getName().endsWith("txt") 
                        || (fileObject.getName().endsWith("htm"))
                        || (fileObject.getName().endsWith("html")))
                {
                    mIndent = getIndent(fileObject);
                    
                    modifiedName = getModifiedName(fileObject.getName());                
                    mNodeId = modifiedName.substring(0, modifiedName.lastIndexOf('.'));     
                    fileName = fileObject.getName().replaceAll(",", "");
                    fileName = fileObject.getName().replaceAll("\'", "\\\\\'");
                    mNodeName = fileName.substring(0, fileName.lastIndexOf('.'));
                    mNodeType = "file";
                    mNodeContentLocation = mNodeId;
                    if (mNodeId.length() > 60)
                    {
                        mNodeContentLocation = mNodeId.substring(0, 60);
                    }
                    mNodeContentLocation += "_t";
                    
                    mNodeParent = getParentFolder();
                    
                    insertSQL = createInsertIntoMenuHierarchySQL(mNodeId, mNodeName, mNodeType, mNodeParent, mNodeContentLocation);
                    System.out.println(mIndent + "- File >" + insertSQL + "<");
                    
                    // insert into the menu hierarchy table
                    mStatement.executeUpdate(insertSQL);
                    
                    // create the menu hierarchy table
                    mStatement.executeUpdate(createReadableItemTableSQL(mNodeContentLocation));
                    
                    fstream = new FileInputStream(fileObject);
                    in = new DataInputStream(fstream);
                    br = new BufferedReader(new InputStreamReader(in));
                    
                    String strLine;
                    
                    final ArrayList<String> list = new ArrayList<String>();
                    
                    while ((strLine = br.readLine()) != null)
                    {
                        final String trimmedLine = strLine.trim();
                        if (!trimmedLine.isEmpty())
                        {
                            list.add(trimmedLine);
                        }
                    }
                              
                    String lineToInsert = "";
                    int i = 0;
                    for (String line : list)
                    {
                        // increment the last entered id
                        mRowId++;
                        
                        // escape single quotes
                        lineToInsert = line.replaceAll("\'", "\\\\\'");

                        final String sql = "insert into " + mNodeContentLocation + " (id, content, row_created_dttm) values (" + mRowId + ", '" + lineToInsert + "', CURRENT_TIMESTAMP)";
                        System.out.println(i + " - SQL >" + sql + "<");
                        final int k = mStatement.executeUpdate(sql);
                        
                        i++;
                    }                    
                }
            }
        }
        catch (Exception ex)
        {
            ex.printStackTrace();
            System.out.println("Error occurred. mNodeId >" + mNodeId + "<");
            throw new RuntimeException(ex);
        }
    }
    
    private static String getMenuHierarchyTableSQL()
    {
        StringBuilder stringBuilder = new StringBuilder();
        
        //stringBuilder.append("drop table `menu_hierarchy_t`;");
        stringBuilder.append("CREATE TABLE `menu_hierarchy_t` (");
        stringBuilder.append("        `id` int(11) NOT NULL AUTO_INCREMENT,");
        stringBuilder.append("        `node_id` varchar(100) NOT NULL,");
        stringBuilder.append("        `node_name` varchar(100) NOT NULL,");
        stringBuilder.append("        `node_type` varchar(45) DEFAULT NULL,");
        stringBuilder.append("        `node_parent_id` varchar(100) NOT NULL,");
        stringBuilder.append("        `node_content_location` varchar(100) DEFAULT NULL,");
        stringBuilder.append("        `row_created_by` varchar(45) NOT NULL DEFAULT 'mahan',");
        stringBuilder.append("       `row_created_dttm` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',");
        stringBuilder.append("        `row_updated_by` varchar(45) NOT NULL DEFAULT 'mahan',");
        stringBuilder.append("        `row_updated_dttm` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,");
        stringBuilder.append("        PRIMARY KEY (`id`),");
        stringBuilder.append("        UNIQUE KEY `id_UNIQUE` (`id`)");
        stringBuilder.append("      ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8");
        
        return stringBuilder.toString();
    }
    
    private static String createReadableItemTableSQL(final String aTableName)
    {
        final StringBuilder stringBuilder = new StringBuilder();

        stringBuilder.append("CREATE TABLE `");
        stringBuilder.append(aTableName);
        stringBuilder.append("` (");
        stringBuilder.append("   `id` int(11) NOT NULL,");
        stringBuilder.append("   `content` text DEFAULT NULL,");
        stringBuilder.append("   `row_created_by` varchar(45) NOT NULL DEFAULT 'mahan',");
        stringBuilder.append("   `row_created_dttm` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',");
        stringBuilder.append("   `row_updated_by` varchar(45) NOT NULL DEFAULT 'mahan',");
        stringBuilder.append("   `row_updated_dttm` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,");
        stringBuilder.append("   PRIMARY KEY (`id`),");
        stringBuilder.append("   UNIQUE KEY `id_UNIQUE` (`id`)");
        stringBuilder.append("   ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
        
        return stringBuilder.toString();
    }    
    
    private static String createInsertIntoMenuHierarchySQL(final String aNodeId, 
                                                           final String aNodeName, 
                                                           final String aNodeType, 
                                                           final String aNodeParent, 
                                                           final String aNodeContentLocation)
    {
        StringBuilder sqlBuilder = new StringBuilder();
        
        sqlBuilder.append("insert into menu_hierarchy_t ");
        sqlBuilder.append("(node_id, node_name, node_type, node_parent_id, node_content_location, row_created_dttm) ");
        sqlBuilder.append("values ('");
        sqlBuilder.append(aNodeId);
        sqlBuilder.append("', '");
        sqlBuilder.append(aNodeName);
        sqlBuilder.append("', ");
        
        if (aNodeType != null)
        {
            sqlBuilder.append("'");
            sqlBuilder.append(aNodeType);
            sqlBuilder.append("', '");
        }
        else
        {
            sqlBuilder.append(aNodeType);
            sqlBuilder.append(", '");            
        }
        
        sqlBuilder.append(aNodeParent);
        sqlBuilder.append("', ");
        
        if (aNodeContentLocation != null)
        {
            sqlBuilder.append("'");
            sqlBuilder.append(aNodeContentLocation);
            sqlBuilder.append("', ");
        }
        else
        {
            sqlBuilder.append(aNodeContentLocation);
            sqlBuilder.append(", ");            
        }
        
        sqlBuilder.append("CURRENT_TIMESTAMP)");
        
        return sqlBuilder.toString();
    }
    
    public static void main(String[] args)
    {    	    	
        FileInputStream fstream = null;
        DataInputStream in = null;
        BufferedReader br = null;
        
        try
        {
        	if ((args != null) && (args.length != 1))
        	{
        		throw new Exception("There should be at least 1 argument:\n 1) Location of folder structure");
        	}
        	
        	final String folderPath = args[0];
        	
            Class.forName("com.mysql.jdbc.Driver");
            mConnection = DriverManager.getConnection("jdbc:mysql://localhost:3306/ootwdb", "root", "password");
            mStatement = mConnection.createStatement();           
                              
            // create the menu hierarchy table
            mStatement.executeUpdate(getMenuHierarchyTableSQL());
                        
            FolderStructureToDB traversal = new FolderStructureToDB(new File(folderPath));
            traversal.traverse();
            
            /*fstream = new FileInputStream(fileLocation);
            in = new DataInputStream(fstream);
            br = new BufferedReader(new InputStreamReader(in));
            
            String strLine;
            
            final ArrayList<String> list = new ArrayList<String>();
            
            while ((strLine = br.readLine()) != null)
            {
            	final String trimmedLine = strLine.trim();
            	if (!trimmedLine.isEmpty())
            	{
                    list.add(trimmedLine);
            	}
            }
                      
            String lineToInsert = "";
            int i = 0;
            for (String line : list)
            {
            	// increment the last entered id
            	lastEnteredId++;
            	
                // escape single quotes
            	lineToInsert = line.replaceAll("\'", "\\\\\'");

                final String sql = "insert into " + dbTable + " (id, content, row_created_dttm) values (" + lastEnteredId + ", '" + lineToInsert + "', CURRENT_TIMESTAMP)";
                System.out.println(i + " - SQL >" + sql + "<");
                final int k = st.executeUpdate(sql);
                
                i++;
            }*/
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            try
            {
                if (br != null)
                {
                    br.close();
                }
            
                if (in != null)
                {
                    in.close();
                }
                
                if (fstream != null)
                {
                    fstream.close();
                }                
                
                if (mStatement != null)
                {
                    mStatement.close();
                }
                
                if (mConnection != null)
                {
                    mConnection.close();
                }                
            }
            catch (IOException ioEx)
            {
                ioEx.printStackTrace();
            }
            catch (SQLException sqlEx)
            {
                sqlEx.printStackTrace();
            }             
        }
    }
}
